﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace Server
{
    public class UserDB
    {
        /// <summary>
        /// The datasource to access the database
        /// </summary>
        private const string m_DATASOURCE = "Data Source = FMP.sqlite";

        #region Login/Logout

        /// <summary>
        /// Login a user in
        /// </summary>
        /// <param name="username">The username</param>
        /// <param name="password">The password</param>
        /// <returns>True is sucessfull login, otherwise false for failed login</returns>
        public bool LoginUser(string username, string password)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();
                    const string sqlGetUsernamePassword = "SELECT password FROM tblUsers WHERE username = @Username";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlGetUsernamePassword, conn))
                    {
                        cmd.Parameters.AddWithValue("@Username", username);
                        SQLiteDataReader reader = cmd.ExecuteReader();

                        if (reader.Read())
                        {
                            string pass = reader["password"] as string;
                            if (pass == null)
                            {
                                System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} does not exist.", DateTime.Now, username));
                                return false;
                            }
                            else if (pass != password)
                            {
                                System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} gave an incorrect password.", DateTime.Now, username));
                                return false;
                            }
                        }
                        else
                        {
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} does not exist.", DateTime.Now, username));
                            return false;
                        }

                        reader.Close();
                    }

                    const string sqlUpdateUserLoginStatus = "UPDATE tblUsers SET loggedIn = 1 WHERE username = @Username";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlUpdateUserLoginStatus, conn))
                    {
                        // TODO: wrap in transaction if it updates more than one, so that it can be reversted
                        cmd.Parameters.AddWithValue("@Username", username);
                        if (cmd.ExecuteNonQuery() == 1)
                        {
                            // Update sucessful
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} sucessfully logged in.", DateTime.Now, username));
                            return true;
                        }
                        else
                        {
                            // Some sort of error happend eith updated to many or to few
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} updated to many or to few users.", DateTime.Now, username));
                            return false;
                        }
                    }
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                    return false;
                }
            }
        }

        /// <summary>
        /// Logs a user out of the database
        /// </summary>
        /// <param name="username">The username</param>
        public void LogoutUser(string username)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();
                    const string sqlUpdateUserLoginStatus = "UPDATE tblUsers SET loggedIn = 0 WHERE username = @Username";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlUpdateUserLoginStatus, conn))
                    {
                        cmd.Parameters.AddWithValue("@Username", username);
                        if (cmd.ExecuteNonQuery() == 1)
                        {
                            // Update sucessful
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} sucessfully logged out.", DateTime.Now, username));
                        }
                        else
                        {
                            // Some sort of error happend eith updated to many or to few
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} updated to many or to few users.", DateTime.Now, username));
                        }
                    }
                    conn.Close();
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                }
            }
        }

        #endregion

        public bool addUser(string name, string password)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();
                    const string sqlNewUser = "Insert into tblUsers(username, password, loggedIn) Values(@Username,@Password, 0)";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlNewUser, conn))
                    {
                        cmd.Parameters.AddWithValue("@Username", name);
                        cmd.Parameters.AddWithValue("@Password", password);
                        int insert = cmd.ExecuteNonQuery();
                        if (insert == 1)
                        {
                            return true;
                        }
                        else
                            return false;
                        
                            
                        
                    }
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                    return false;
                }

            }
        }

        public bool UserExist(string username)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();
                    const string sqlGetUsernamePassword = "SELECT * FROM tblUsers WHERE username = @Username";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlGetUsernamePassword, conn))
                    {
                        cmd.Parameters.AddWithValue("@Username", username);
                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                return true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                    }
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                    return false;
                }
            }
        }
    }
}
